﻿CREATE TRIGGER tr_Car_DELETE
ON dbo.t_Car 
FOR DELETE
AS

DECLARE @LoanID varchar(50),@VIN sysname
SELECT @LoanID = ci.LoanID,@VIN = ci.VIN FROM t_CarInfo ci INNER JOIN deleted d ON ci.VIN = d.VIN

IF NOT @LoanID IS NULL BEGIN
	ROLLBACK TRAN
	RAISERROR('VIN:%s is used in LoanID:%s',16,1,@VIN,@LoanID)
	RETURN
END

IF EXISTS(SELECT * FROM deleted d INNER JOIN t_Tracker_Car tc ON d.VIN = tc.VIN)BEGIN
	ROLLBACK TRAN
	RAISERROR('Can''t delete car while tracker is installed.',16,1)
	RETURN
END
/*
DECLARE @TrackerID varchar(50)
SELECT @TrackerID = tc.TrackerID,@VIN = tc.VIN FROM t_TrackerCar tc INNER JOIN deleted d ON tc.VIN = d.VIN
IF NOT @TrackerID IS NULL BEGIN
	ROLLBACK TRAN
	RAISERROR('Tracker %s is still installed on VIN:%s',16,1,@TrackerID,@VIN)
	RETURN
END
*/
